﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
using INTEX.DataBase.Tools;
namespace WPFExcelReport
{

    public class MVTCREDITData : BaseFunction
    {
        
        public MVTCREDITData()
            : base()
        {

        }
        public MVTCREDITData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetCREDIT(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "CREDIT";
            string KUNDNR = dwABPOS["KUNDNR"].ToString().Trim();

            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT TO_NUMBER(X.KDKUNDNR) AS CODE,TRIM(X.KDKUNDNA) AS CUSTOMER, "
                    + " Z.CURRENCY,X.KDZUSKRED AS CREDIT,sum(decode(status,'J',0,Z.ORDERVALUE)) as ORDERVALUE, "
                    + " sum(decode(status,'J',0,Z.ADEPOSIT)) as ADEPOSIT, "
                    + " sum(decode(status,'J',0,Z.GOODSVALUE)) as GOODSVALUE, "
                    + " sum(decode(status,'J',0,Z.ABEFORSHIP)) as ABEFORSHIP, "
                    + " sum(decode(status,'J',0,Z.AVRVALUES)) as AVRVALUES, "
                    + " SUM(DECODE(STATUS,'J',0,decode(GOODSVALUE,0,ORDERVALUE-ADEPOSIT, "
                    + " (CASE WHEN GOODSVALUE > ORDERVALUE then "
                    + "  ROUND(GOODSVALUE - ADEPOSIT - ABEFORSHIP - AVRVALUES + "
                    + "  (ABEFORSHIP / GOODSVALUE * AVRVALUES) +(ADEPOSIT / GOODSVALUE * AVRVALUES)) "
                    + "  ELSE ROUND(ORDERVALUE - ADEPOSIT - ABEFORSHIP - AVRVALUES + "
                    + "  (ABEFORSHIP / GOODSVALUE * AVRVALUES) +(ADEPOSIT / ORDERVALUE * AVRVALUES)) "
                    + "  END)))) AS REMAINVALUE, "
                    + " decode(X.KDZUSKRED,0,0,X.KDZUSKRED-SUM(DECODE(STATUS,'J',0,decode(GOODSVALUE,0,ORDERVALUE-ADEPOSIT, "
                    + " (CASE WHEN GOODSVALUE > ORDERVALUE then "
                    + "  ROUND(GOODSVALUE - ADEPOSIT - ABEFORSHIP - AVRVALUES + "
                    + "  (ABEFORSHIP / GOODSVALUE * AVRVALUES) +(ADEPOSIT / GOODSVALUE * AVRVALUES)) "
                    + "  ELSE ROUND(ORDERVALUE - ADEPOSIT - ABEFORSHIP - AVRVALUES + "
                    + "  (ABEFORSHIP / GOODSVALUE * AVRVALUES) +(ADEPOSIT / ORDERVALUE * AVRVALUES)) "
                    + "  END))))) AS REMAINCREDIT "
                    + " FROM KDSTAMM X, "
                    + " (select MIN(C.VKPERL) AS STATUS,A.VKKAUFKRS || '-' || TO_NUMBER(A.VKKAUFLFD) AS CO, "
                    + " A.VKKNEUDAT AS ENTRYDATE,A.VKKKUNDNR AS CSCODE, "
                    + " round(A.VKKGESWERT / (A.VKKKURS * 0.0001)) AS ORDERVALUE,A.VKKWAEHRSL AS CURRENCY, "
                    + " A.VKKZAHLBED AS PAYMENT,A.VKKOPITEM AS OPENITEM,trim(B.ZABBEZ) AS DES, "
                    + " ROUND(A.VKKGESWERT / (A.VKKKURS * 0.0001) * B.zabskonp1 * 0.0001) AS PDEPOSIT, "
                    + " NVL((SELECT GLOAMICUC * 0.01 FROM GLOPITEM G "
                    + " WHERE G.GLOMANDANT = A.VKKMANDANT AND G.GLOACCOUNT = TO_NUMBER(A.VKKKUNDNR) "
                    + "  AND G.GLOITEM = A.VKKOPITEM), 0) AS ADEPOSIT, "
                    + " ROUND(SUM((case when C.VKPZUGET > 0 then(C.VKPPOSWERT / (C.VKPKURS * 0.0001)) - "
                    + " (C.VKPMENGE1 * 0.01 * c.vkppreis * 0.001)  else  0 end) +  "
                    + " (C.VKPZUGET * 0.01 * c.vkppreis * 0.001))) AS GOODSVALUE, "
                    + " ROUND(SUM((case when C.VKPZUGET > 0 then (C.VKPPOSWERT / (C.VKPKURS * 0.0001)) - "
                    + " (C.VKPMENGE1 * 0.01 * c.vkppreis * 0.001) else  0 end) +  "
                    + " (C.VKPZUGET * 0.01 * c.vkppreis * 0.001)) *  B.zabskonp2 * 0.0001) AS PBEFORSHIP, "
                    + " nvl((SELECT SUM(VRKWAWERT * 0.01) FROM VRKOPF V WHERE V.VRKMANDANT = A.VKKMANDANT "
                    + " AND TRIM(V.VRKADINVNO) = TRIM(A.VKKOPITEM) AND V.VRKFIBUKZ <> 'S' "
                    + " AND V.VRKREART = 'BE'),0) AS ABEFORSHIP, "
                    + " ROUND(SUM((case  when C.VKPZUGET > 0 then (C.VKPPOSWERT / (C.VKPKURS * 0.0001)) - "
                    + " (C.VKPMENGE1 * 0.01 * c.vkppreis * 0.001)else 0 end) +  "
                    + " (C.VKPZUGET * 0.01 * c.vkppreis * 0.001)) * B.zabvorzz * 0.0001) AS PAFTERSHIP, "
                    + " nvl((SELECT SUM(A1.VRPGESWERT * 0.001) FROM VRPOS A1, VRKOPF B1 "
                    + " WHERE A1.VRPMANDANT = B1.VRKMANDANT  AND A1.VRPRENR = B1.VRKRENR "
                    + "  AND B1.VRKFIBUKZ <> 'S'  AND B1.VRKREART = 'RE'  AND A1.VRPMANDANT = A.VKKMANDANT "
                    + "  AND A1.VRPAUFKRS = A.VKKAUFKRS  AND A1.VRPAUFLFD = A.VKKAUFLFD),0) AS VRTOTVALUES, "
                    + " nvl((SELECT SUM(A1.VRPGESWERT * 0.001)  FROM VRPOS A1, VRKOPF B1 "
                    + "  WHERE A1.VRPMANDANT = B1.VRKMANDANT AND A1.VRPRENR = B1.VRKRENR "
                    + "  AND B1.VRKFIBUKZ <> 'S' AND B1.VRKREART = 'RE' AND B1.VRKKOMPL = 'J' "
                    + "  AND A1.VRPMANDANT = A.VKKMANDANT AND A1.VRPAUFKRS = A.VKKAUFKRS "
                    + "    AND A1.VRPAUFLFD = A.VKKAUFLFD),0) AS AVRVALUES,B.zabntage AS AFTERDAYS "
                    + " from vkkopf a, ZABED B, VKPOS C "
                    + " where A.VKKMANDANT = C.VKPMANDANT AND A.VKKSA = C.VKPSA "
                    + " AND A.VKKAUFKRS = C.VKPAUFKRS AND A.VKKAUFLFD = C.VKPAUFLFD "
                    + " AND A.VKKMANDANT = B.ZABMANDANT AND A.VKKZAHLBED = B.ZABNR "
                    + " AND a.vkksa <> 'AJ' AND B.ZABSPRACHKZ = 'V ' "
                    + " GROUP BY A.VKKMANDANT, A.VKKAUFKRS,A.VKKAUFLFD,A.VKKNEUDAT, "
                    + " A.VKKKUNDNR,A.VKKGESWERT,A.VKKKURS, "
                    + " A.VKKWAEHRSL,A.VKKZAHLBED,A.VKKOPITEM,B.ZABBEZ,B.zabskonp1, "
                    + " B.zabskonp2,B.zabvorzz,B.zabntage) Z "
                    + " WHERE X.KDKUNDNR = Z.CSCODE(+) "
                    + " and (TRIM(X.KDKUNDNA) = '" + KUNDNR + "' OR '" + KUNDNR + "' = 'AAA' ) "
                    + " GROUP BY X.KDKUNDNR,X.KDKUNDNA,Z.CURRENCY,X.KDZUSKRED "
                    + " ORDER BY 1 "                  
                    ;
                
                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["CUSTOMER"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["CUSTOMER"] = value;
                    }
                }                
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        
        public DataSet BuildMVTCREDITData(DataRow dwUI)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetCREDIT(dwUI);
                setReturn.Merge(setResult);                
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}